import pandas as pd
import numpy as np
from openpyxl import Workbook, load_workbook
from openpyxl.styles import Alignment, Font, Border, Side
from openpyxl.utils import get_column_letter
import os
from datetime import datetime
from openpyxl.cell.cell import Cell
from openpyxl.styles import NamedStyle, numbers

def process_orders():
    # 读取订单文件
    orders_file = "订单.xlsx"
    template_file = "模版.xlsx"
    output_file = "企业结算对账单.xlsx"
    
    # 读取订单数据
    df = pd.read_excel(orders_file, dtype={'订单号': str})  # 确保订单号作为字符串读取
    
    # 过滤掉空行 - 只在汇总时过滤退款订单，明细需要保留所有订单
    df_all = df[df['企业名称'].notna()]
    
    # 按企业名称和企业结算主体分组
    company_groups = df_all.groupby(['企业名称', '企业结算主体'])
    
    # 创建输出工作簿
    output_wb = Workbook()
    # 删除默认创建的sheet
    output_wb.remove(output_wb.active)
    
    # 创建文本样式
    text_style = NamedStyle(name='text_style')
    text_style.number_format = '@'
    
    # 读取模板文件获取格式参考
    template_wb = load_workbook(template_file)
    summary_sheet = template_wb.worksheets[0]  # 汇总页
    detail_sheet = template_wb.worksheets[1]   # 明细页
    
    # 处理每个企业和结算主体的组合
    for (company, seller), group_data in company_groups:
        # 过滤该企业和结算主体组合的数据 - 汇总数据不包含退款订单
        company_summary_data = group_data[group_data['订单状态'] != '已退款'].copy()
        # 明细数据包含所有订单（包括退款订单）
        company_detail_data = group_data.copy()
        
        # 生成sheet名称，根据企业名称和结算主体区分
        seller_short_name = seller.split('微行')[0] if '微行' in seller else seller[:2]
        summary_name = f"{company}{seller_short_name}汇总"
        detail_name = f"{company}{seller_short_name}明细"
        
        # 创建汇总页
        summary = output_wb.create_sheet(summary_name)
        
        # 复制模板格式到汇总页
        copy_sheet_format(summary_sheet, summary)
        
        # 填充汇总页数据
        fill_summary_sheet(summary, company, seller, company_summary_data)
        
        # 创建明细页
        detail = output_wb.create_sheet(detail_name)
        
        # 复制模板格式到明细页
        copy_sheet_format(detail_sheet, detail)
        
        # 填充明细页数据
        fill_detail_sheet(detail, company_detail_data, text_style)
    
    # 保存结果
    output_wb.save(output_file)
    print(f"已生成企业结算对账单: {output_file}")

def copy_sheet_format(source, target):
    """复制工作表的基本格式"""
    # 复制列宽
    for col in range(1, source.max_column + 1):
        column_letter = get_column_letter(col)
        if source.column_dimensions[column_letter].width:
            target.column_dimensions[column_letter].width = source.column_dimensions[column_letter].width
    
    # 复制行高
    for row in range(1, source.max_row + 1):
        if source.row_dimensions[row].height:
            target.row_dimensions[row].height = source.row_dimensions[row].height
    
    # 复制合并单元格
    for merged_cell_range in source.merged_cells.ranges:
        target.merge_cells(str(merged_cell_range))

def fill_summary_sheet(sheet, company, seller, data):
    """填充汇总页数据"""
    # 设置标题
    sheet['A1'] = "企业对账结算单"
    sheet.merge_cells('A1:H1')
    sheet['A1'].alignment = Alignment(horizontal='center', vertical='center')
    sheet['A1'].font = Font(bold=True, size=14)
    
    # 设置企业信息
    sheet['A2'] = "购买方："
    sheet['B2'] = company  # 购买方
    sheet['F2'] = "销售方："
    sheet['G2'] = seller   # 销售方
    
    # 设置账单周期和生成日期
    sheet['A3'] = "账单周期："
    sheet['B3'] = "2025-04-01 00:00:00 至 2025-04-30 23:59:59"  # 更新账单周期
    sheet['F3'] = "账单生成日期："
    sheet['G3'] = "2025-05-09"  # 更新账单生成日期
    
    # 设置表头
    headers = ["对账事项", "结算类型", "商品名称", "消费数量", "消费金额（元）", 
               "本期应下发账单赠金", "本期应下发手动发金", "开票金额（元）"]
    
    for col, header in enumerate(headers, 1):
        sheet.cell(row=5, column=col).value = header
        sheet.cell(row=5, column=col).alignment = Alignment(horizontal='center', vertical='center')
        sheet.cell(row=5, column=col).font = Font(bold=True)
    
    # 按商品名称分组汇总数据
    grouped_data = data.groupby('商品')
    
    # 填充数据行
    row_idx = 6
    for product, group in grouped_data:
        sheet.cell(row=row_idx, column=1).value = "能源交易对账"  # 对账事项
        sheet.cell(row=row_idx, column=2).value = group['企业结算类型'].iloc[0] if '企业结算类型' in group.columns else "预付款"  # 结算类型
        sheet.cell(row=row_idx, column=3).value = product         # 商品名称
        
        # 计算汇总数据
        total_quantity = sum(float(q) if isinstance(q, (int, float, str)) and q != "" else 0 
                            for q in group['数量'])
        sheet.cell(row=row_idx, column=4).value = total_quantity  # 消费数量
        
        total_amount = sum(float(a) if isinstance(a, (int, float, str)) and a != "" else 0 
                          for a in group['订单总金额'])
        sheet.cell(row=row_idx, column=5).value = total_amount    # 消费金额（元）
        
        # 赠金计算 - 更新取数逻辑
        # 本期应下发账单赠金 对应 A文件中的 赠金金额
        gift_amount = sum(float(p) if isinstance(p, (int, float, str)) and p != "" and pd.notna(p) else 0 
                         for p in group['赠金金额'])
        
        # 本期应下发手动赠金 全部置为 "-"
        manual_gift = "-"
        
        # 设置赠金数据
        sheet.cell(row=row_idx, column=6).value = gift_amount if gift_amount > 0 else "-"  # 本期应下发账单赠金
        sheet.cell(row=row_idx, column=7).value = manual_gift  # 本期应下发手动发金
        
        # 开票金额计算 - 更新为新的计算方式
        # 开票金额 = 实付金额 - 实付赠金金额，前提是企业合作模式是转售模式，否则置为 "-"
        if '企业合作模式' in group.columns and group['企业合作模式'].iloc[0] == '转售模式':
            total_paid = sum(float(p) if isinstance(p, (int, float, str)) and p != "" and pd.notna(p) else 0 
                            for p in group['实付金额'])
            total_gift = sum(float(g) if isinstance(g, (int, float, str)) and g != "" and pd.notna(g) else 0 
                            for g in group['实付赠金金额'])
            total_invoice = total_paid - total_gift
            sheet.cell(row=row_idx, column=8).value = total_invoice if total_invoice > 0 else "-"  # 开票金额（元）
        else:
            sheet.cell(row=row_idx, column=8).value = "-"  # 开票金额（元）
        
        row_idx += 1
    
    # 添加合计行
    sheet.cell(row=row_idx, column=1).value = "合计"
    sheet.cell(row=row_idx, column=1).alignment = Alignment(horizontal='center', vertical='center')
    
    # 计算各列合计
    for col in range(4, 9):
        values = []
        for row in range(6, row_idx):
            cell_value = sheet.cell(row=row, column=col).value
            if cell_value != "-" and cell_value is not None:
                values.append(float(cell_value) if isinstance(cell_value, (int, float, str)) else 0)
        
        total = sum(values)
        sheet.cell(row=row_idx, column=col).value = total if total > 0 else "-"
    
    # 添加备注和签章
    row_idx += 2
    sheet.cell(row=row_idx, column=1).value = "1、以上结算，请贵司在收到账单后5日内予以确认，并盖章回传。"
    row_idx += 1
    sheet.cell(row=row_idx, column=1).value = "2、我公司收到贵公司确认盖章对账单后，将予以当月开具发票。"
    
    row_idx += 3
    sheet.cell(row=row_idx, column=1).value = "购买方（盖章）："
    sheet.cell(row=row_idx, column=2).value = company
    
    sheet.cell(row=row_idx, column=6).value = "销售方（盖章）："
    sheet.cell(row=row_idx, column=7).value = seller

def fill_detail_sheet(sheet, data, text_style):
    """填充明细页数据"""
    # 设置表头
    headers = ["订单号", "下单时间", "支付时间", "订单状态", "商品名称", "油站价", "数量", 
               "订单总金额", "企业价", "实付金额（含赠金）", "结算金额（不含赠金）", 
               "油站名称", "车牌号", "司机", "结算主体"]
    
    for col, header in enumerate(headers, 1):
        sheet.cell(row=1, column=col).value = header
        sheet.cell(row=1, column=col).alignment = Alignment(horizontal='center', vertical='center')
        sheet.cell(row=1, column=col).font = Font(bold=True)
    
    # 设置第一列为文本格式
    for row in range(2, len(data) + 2):
        cell = sheet.cell(row=row, column=1)
        cell.number_format = '@'  # 设置为文本格式
    
    # 填充数据行
    for row_idx, (_, row) in enumerate(data.iterrows(), 2):
        # 确保订单号作为文本，但不添加单引号前缀
        cell = sheet.cell(row=row_idx, column=1)
        cell.value = str(row['订单号'])  # 直接转换为字符串，不添加单引号
        
        sheet.cell(row=row_idx, column=2).value = row['下单时间']
        sheet.cell(row=row_idx, column=3).value = row['支付时间']
        sheet.cell(row=row_idx, column=4).value = row['订单状态']
        sheet.cell(row=row_idx, column=5).value = row['商品']
        sheet.cell(row=row_idx, column=6).value = row['油站价']
        sheet.cell(row=row_idx, column=7).value = row['数量']
        sheet.cell(row=row_idx, column=8).value = row['订单总金额']
        sheet.cell(row=row_idx, column=9).value = row['企业价']
        sheet.cell(row=row_idx, column=10).value = row['实付金额']
        
        # 修改结算金额（不含赠金）的计算逻辑
        # 从原来的应结金额改为实付金额-实付赠金金额
        settlement_amount = float(row['实付金额']) - float(row['实付赠金金额']) if pd.notna(row['实付金额']) and pd.notna(row['实付赠金金额']) else 0
        sheet.cell(row=row_idx, column=11).value = settlement_amount if settlement_amount > 0 else "-"
        
        sheet.cell(row=row_idx, column=12).value = row['油站名称']
        sheet.cell(row=row_idx, column=13).value = row['车牌号']
        sheet.cell(row=row_idx, column=14).value = row['司机']
        sheet.cell(row=row_idx, column=15).value = row['企业结算主体']

if __name__ == "__main__":
    process_orders() 